-- Database: xperformancerobotics_db

-- Table for Departments/Heads/Categories
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for Levels (with infinite depth using parent_level_id)
CREATE TABLE levels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT NOT NULL,
    parent_level_id INT NULL, -- Self-referencing for sub-levels
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_level_id) REFERENCES levels(id) ON DELETE CASCADE
);

-- Table for Employees
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_uuid VARCHAR(36) NOT NULL UNIQUE, -- Using UUID for employee_id for better uniqueness
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL, -- For employee login
    position VARCHAR(255),
    reporting_to_employee_id INT NULL, -- Self-referencing for reporting structure
    location VARCHAR(255),
    linkedin_profile VARCHAR(255),
    department_id INT NOT NULL,
    level_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reporting_to_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT,
    FOREIGN KEY (level_id) REFERENCES levels(id) ON DELETE RESTRICT
);

-- Table for Registered Website Users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    verification_code VARCHAR(6) NULL, -- For email verification
    is_verified BOOLEAN DEFAULT FALSE,
    country VARCHAR(255),
    postal_code VARCHAR(20),
    phone_number VARCHAR(20),
    company VARCHAR(255),
    social_profile_linkedin VARCHAR(255),
    website_link VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table for User Requirements/Messages
CREATE TABLE user_requirements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    department_id INT NOT NULL,
    level_id INT NOT NULL,
    requirement_text TEXT NOT NULL,
    submission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_employee_id INT NULL, -- Employee assigned to this requirement
    status ENUM('pending', 'assigned', 'chat_scheduled', 'video_scheduled', 'closed', 'canceled') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT,
    FOREIGN KEY (level_id) REFERENCES levels(id) ON DELETE RESTRICT,
    FOREIGN KEY (assigned_employee_id) REFERENCES employees(id) ON DELETE SET NULL
);

-- Table for Chat Appointments
CREATE TABLE chat_appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requirement_id INT NOT NULL,
    user_id INT NOT NULL,
    employee_id INT NOT NULL,
    scheduled_datetime DATETIME NOT NULL,
    chat_link VARCHAR(2048) NOT NULL, -- Link for Element.io chat
    status ENUM('scheduled', 'completed', 'canceled') DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requirement_id) REFERENCES user_requirements(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Table for Video Calls
CREATE TABLE video_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    requirement_id INT NOT NULL,
    user_id INT NOT NULL,
    employee_id INT NOT NULL,
    jitsi_link VARCHAR(2048) NOT NULL, -- Link for Jitsi video call
    initiated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requirement_id) REFERENCES user_requirements(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Optional: Add indexes for frequently queried columns to improve performance
CREATE INDEX idx_levels_department_id ON levels(department_id);
CREATE INDEX idx_levels_parent_level_id ON levels(parent_level_id);
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_employees_level_id ON employees(level_id);
CREATE INDEX idx_employees_reporting_to ON employees(reporting_to_employee_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_user_requirements_user_id ON user_requirements(user_id);
CREATE INDEX idx_user_requirements_department_id ON user_requirements(department_id);
CREATE INDEX idx_user_requirements_level_id ON user_requirements(level_id);
CREATE INDEX idx_user_requirements_assigned_employee_id ON user_requirements(assigned_employee_id);
CREATE INDEX idx_chat_appointments_user_id ON chat_appointments(user_id);
CREATE INDEX idx_chat_appointments_employee_id ON chat_appointments(employee_id);
CREATE INDEX idx_video_calls_user_id ON video_calls(user_id);
CREATE INDEX idx_video_calls_employee_id ON video_calls(employee_id);
